package com.situ.store.util;

import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

import javax.sql.DataSource;

import com.alibaba.druid.pool.DruidDataSourceFactory;

public class DBCP {

	private static DataSource dataSource;

	static {
		Properties pp = new Properties();
		try {
			// 从 db.properties 文件中加载
			pp.load(Thread.currentThread().getContextClassLoader().getResourceAsStream("db.properties"));

			dataSource = DruidDataSourceFactory.createDataSource(pp);

		} catch (Exception e) {
			System.out.println("初始化数据库连接池失败！");
			e.printStackTrace();
		}
	}

	/**
	 * 用于执行DQL
	 * 
	 * @param <T>    查询结果类型
	 * @param sql
	 * @param clazz  查询结果类型
	 * @param params
	 * @return
	 */
	public static <T> List<T> excuteDQL(String sql, Class<T> clazz, Object... params) {
		List<T> list = new ArrayList<>();
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			conn = getConnection();
			ps = conn.prepareStatement(sql);
			if (params != null) {
				for (int i = 0; i < params.length; i++) {
					ps.setObject(i + 1, params[i]);
				}
			}
			rs = ps.executeQuery();
			ResultSetMetaData rsmd = rs.getMetaData(); // 元数据
			while (rs.next()) {
				T t = clazz.newInstance();
				// i 表示第几列
				for (int i = 1; i <= rsmd.getColumnCount(); i++) {
					Object value = rs.getObject(i); // 第 i 列的值
					String lable = rsmd.getColumnLabel(i); // 第 i 列表头名
//					lable = StringUtil.lineToHump(lable);
					try {
						Field field = clazz.getDeclaredField(StringUtil.lineToHump(lable)); // 找到与表头名重名的成员变量
						field.setAccessible(true);
						field.set(t, value);
					} catch (Exception e) {
						e.printStackTrace();
						System.out.println(lable + "这一列未找到！");
					}
				} // end for
				list.add(t);
			} // end while
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			close(rs, ps, conn);
		}
		return list;
	}

	/**
	 * 统计[慎用！！！]
	 * @param sql
	 * @param params
	 * @return
	 */
	public static Long executeCount(String sql, Object... params) {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			conn = getConnection();
			ps = conn.prepareStatement(sql);
			if (params != null) {
				for (int i = 0; i < params.length; i++) {
					ps.setObject(i + 1, params[i]);
				}
			}
			rs = ps.executeQuery();
			rs.next(); // 指向结果集第一行
			return rs.getLong(1);
		} catch (Exception e) {
			return 0L;
		} finally {
			close(rs, ps, conn);
		}
	}

	/**
	 * 用于执行DML
	 * 
	 * @param sql
	 * @param params
	 * @return
	 */
	public static int excuteDML(String sql, Object... params) {
		Connection conn = null;
		PreparedStatement ps = null;
		try {
			conn = getConnection();
			ps = conn.prepareStatement(sql);
			if (params != null) {
				for (int i = 0; i < params.length; i++) {
					ps.setObject(i + 1, params[i]);
				}
			}
			return ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			close(ps, conn);
		}
		return 0;
	}

	/**
	 * 用于返回一个 connection 连接对象 【池化思想】
	 * 
	 * @return
	 */
	private static Connection getConnection() throws SQLException {
		return dataSource.getConnection();
	}

	/**
	 * 用于释放数据库资源[DML]
	 * 
	 * @param ps
	 * @param conn
	 */
	private static void close(PreparedStatement ps, Connection conn) {
		close(null, ps, conn);
	}

	/**
	 * 用于释放数据库资源[DQL]
	 * 
	 * @param rs
	 * @param ps
	 * @param conn
	 */
	private static void close(ResultSet rs, PreparedStatement ps, Connection conn) {
		try {
			if (rs != null) {
				rs.close();
			}
			if (ps != null) {
				ps.close();
			}
			if (conn != null) {
				conn.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

}
